<?php


namespace App\Common\Tools;


use Hyperf\DbConnection\Db;

/**
 * mysql 操作类  因为懒。。。。。
 * Class DbTools
 * @package App\Model\tools
 */
class DbTools
{

    /**
     * 模糊搜索
     * sqlLike
     * @param string $attr
     * @param string $value
     * @param string $table
     * @return \Hyperf\Database\Query\Expression
     * date:2021/9/16
     * time:9:46
     * auth：xyc
     */
    public static function sqlLike(string $attr, string $value, string $table = "")
    {
        return Db::raw(sprintf('LOCATE("%s", %s)', $value, $table ? env('DB_PREFIX', "") . $table : "" . $attr));
    }


    /**
     * 模糊搜索
     * sqlLikePro
     * @param string $attr
     * @param string sqlLikePro
     * @param string $table
     * @return array
     * date:2021/9/17
     * time:9:25
     * auth：xyc
     */
    public static function sqlLikePro(string $attr, string $value, string $table = "")
    {
        return [Db::raw(sprintf('LOCATE("%s", %s)', $value, $table ? env('DB_PREFIX', "") . $table : "" . $attr)), ">", 0];
    }


    /**
     * 取别名
     * sqlLike
     * @param string $attr
     * @param string $alias
     * @param string $table
     * @return string
     * date:2021/9/16
     * time:9:46
     * auth：xyc
     */
    public static function alias(string $attr, string $alias, string $table = "")
    {
        return sprintf("%s as %s", $table . $attr, $alias);
    }


    /**
     * 判断是否为null
     * ifnull
     * @param string $attr
     * @param string $defalut
     * @param string $table
     * @return \Hyperf\Database\Query\Expression
     * date:2021/9/16
     * time:9:58
     * auth：xyc
     */
    public static function ifnull(string $attr, string $defalut = "", string $table = "")
    {
        return Db::raw(sprintf("IFNULL(%s, %s)", $table ? env('DB_PREFIX', "") . $table : "" . $attr,
            (is_string($defalut) && empty($defalut)) ?
                "''" : $defalut
        ));
    }


    /**
     * findInSet
     * @param string $attr
     * @param string $value
     * @param string $table
     * @return string
     * date:2021/9/16
     * time:14:34
     * auth：xyc
     */
    public static function findInSet(string $attr, string $value, string $table = "")
    {
        $tA = $table ?: "" . $attr;
        return "FIND_IN_SET('{$value}',$tA)";
    }

    /**
     * 获取两坐标点之间距离
     * @param $lat1 float 纬度1
     * @param $lng1 float 经度1
     * @param $lat2 float 纬度2
     * @param $lng2 float 经度2
     * @param int $len_type 返回长度类型：1千米 2米
     * @return false|float
     */
    public static function getPointDistance(float $lng1, float $lat1, float $lng2, float $lat2, $len_type = 1)
    {
        if (empty($lat1) || empty($lng1) || empty($lat2) || empty($lng2)) {
            return false;
        }
        if ($lat1 >= 90 || $lat2 >= 90) {
            return false;
        }
        $distance = Db::selectOne(sprintf("select st_distance_sphere(point(%f,%f),point(%f,%f)) as distance", $lng1, $lat1, $lng2, $lat2));
        if (!empty($distance) && !empty($distance['distance'])) {
            $distance['distance'] = $len_type == 1 ? $distance['distance'] / 1000 : $distance['distance'];
//            var_dump(round($distance['distance'], 2));
            return round($distance['distance'], 2);
        }
        return false;
    }
}